package com.samsung.svmc.moneymanager.data;

import java.util.ArrayList;
import java.util.Calendar;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.samsung.svmc.moneymanager.object.TaiKhoan;

public class DbTaiKhoan extends DbConnect {

	protected String tag = "SQLConnection";
	protected static DbTaiKhoan instance;
	protected Context context;
	protected DbHelper _help;

	public static final String TABLE = "tbl_taikhoan";
	public static final String Id = "id";
	public static final String SoTk = "so_tk";
	public static final String LoaiTienId = "loaitien_id";
	public static final String SoDuTk = "sodu";
	public static final String HanMucThang = "hanmuc_thang";
	public static final String LoaiTaiKhoan = "loai_tk";
	public static final String NgayNhap = "ngaynhap";
	public static final String NgayCapNhatCuoi = "ngaycapnhatcuoi";
	public static final String GhiChu = "ghichu";
	public static final String Icon = "icon_id";
	
	public DbTaiKhoan(Context c){
		context = c;
	}
	
	public static DbTaiKhoan getInstance(Context c){
		if(instance == null)
			instance = new DbTaiKhoan(c);
		return instance;
	}
	
	public DbTaiKhoan open() throws SQLException{
			_help = new DbHelper(context);
			_Db = _help.getWritableDatabase();
			return this;
	}
	
	public void close(){
		_help.close();
	}
	
	public void insertData(TaiKhoan tk){
		ContentValues cv = new ContentValues();
		cv.put(SoTk, tk.get_SoTk());
		cv.put(LoaiTienId, tk.get_LoaiTienId());
		cv.put(SoDuTk, tk.get_SoDuTk());
		cv.put(HanMucThang, tk.get_HanMucThang());
		cv.put(LoaiTaiKhoan, tk.get_LoaiTkId());
		cv.put(NgayNhap, tk.get_NgayNhap().getTimeInMillis());
		cv.put(NgayCapNhatCuoi, tk.get_NgayCapNhatCuoi().getTimeInMillis());
		cv.put(GhiChu, tk.get_GhiChu());
		cv.put(Icon, tk.IconId);		
		_Db.insert(TABLE, null, cv);
		Log.d(tag, "insert tai khoan thanh cong");
	}
	
	
	public ArrayList<TaiKhoan> getData(){
		ArrayList<TaiKhoan> lv = new ArrayList<TaiKhoan>();
		
		String[] columns = new String[]{
			Id,SoTk,LoaiTienId,SoDuTk,HanMucThang,LoaiTaiKhoan,NgayNhap,NgayCapNhatCuoi,GhiChu,Icon	
		};
		
		Cursor c = _Db.query(TABLE, columns, null, null, null, null, null);
		if(c == null)
			Log.d(tag, "table tai khoan has not data");
		int iId = c.getColumnIndex(Id);
		int iSoTk = c.getColumnIndex(SoTk);
		int iLoaiTienId = c.getColumnIndex(LoaiTienId);
		int iSoDuTk = c.getColumnIndex(SoDuTk);
		int iHanMucThang = c.getColumnIndex(HanMucThang);
		int iLoaiTk = c.getColumnIndex(LoaiTaiKhoan);
		int iNgayNhap = c.getColumnIndex(NgayNhap);
		int iNgayCapNhatCuoi = c.getColumnIndex(NgayCapNhatCuoi);
		int iGhiChu = c.getColumnIndex(GhiChu);
		int iIcon = c.getColumnIndex(Icon);
		for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
			
			TaiKhoan tmp = new TaiKhoan();
			tmp.set_Id(c.getLong(iId));
			tmp.set_GhiChu(c.getString(iGhiChu));
			tmp.set_HanMucThang(c.getDouble(iHanMucThang));
			tmp.set_LoaiTienId(c.getInt(iLoaiTienId));
			tmp.set_LoaiTkId(c.getInt(iLoaiTk));
			Calendar ngaycapnhatcuoi = Calendar.getInstance(), ngaynhap = Calendar.getInstance();
			ngaycapnhatcuoi.setTimeInMillis(c.getLong(iNgayCapNhatCuoi));
			ngaynhap.setTimeInMillis(c.getLong(iNgayNhap));
			tmp.set_NgayCapNhatCuoi(ngaycapnhatcuoi);
			tmp.set_NgayNhap(ngaynhap);
			tmp.set_SoDuTk(c.getDouble(iSoDuTk));
			tmp.set_SoTk(c.getString(iSoTk));
			tmp.IconId = c.getInt(iIcon);
			lv.add(tmp);
			
		}
		c.close();
		return lv;
	}
	
	public TaiKhoan getTaiKhoan(long index){
		TaiKhoan tmp = new TaiKhoan();
		Cursor c = _Db.query(TABLE, null, Id+"=?", new String[]{index+""}, null, null, null);
		if(c == null)
			Log.d(tag, "table tai khoan has not data");
		int iId = c.getColumnIndex(Id);
		int iSoTk = c.getColumnIndex(SoTk);
		int iLoaiTienId = c.getColumnIndex(LoaiTienId);
		int iSoDuTk = c.getColumnIndex(SoDuTk);
		int iHanMucThang = c.getColumnIndex(HanMucThang);
		int iLoaiTk = c.getColumnIndex(LoaiTaiKhoan);
		int iNgayNhap = c.getColumnIndex(NgayNhap);
		int iNgayCapNhatCuoi = c.getColumnIndex(NgayCapNhatCuoi);
		int iGhiChu = c.getColumnIndex(GhiChu);
		int iIcon = c.getColumnIndex(Icon);
		while(c.moveToNext()){
			tmp.set_Id(c.getLong(iId));
			tmp.set_GhiChu(c.getString(iGhiChu));
			tmp.set_HanMucThang(c.getDouble(iHanMucThang));
			tmp.set_LoaiTienId(c.getInt(iLoaiTienId));
			tmp.set_LoaiTkId(c.getInt(iLoaiTk));
			Calendar ngaycapnhatcuoi = Calendar.getInstance(), ngaynhap = Calendar.getInstance();
			ngaycapnhatcuoi.setTimeInMillis(c.getLong(iNgayCapNhatCuoi));
			ngaynhap.setTimeInMillis(c.getLong(iNgayNhap));
			tmp.set_NgayCapNhatCuoi(ngaycapnhatcuoi);
			tmp.set_NgayNhap(ngaynhap);
			tmp.set_SoDuTk(c.getDouble(iSoDuTk));
			tmp.set_SoTk(c.getString(iSoTk));
			tmp.IconId = c.getInt(iIcon);
			break;
		}
		c.close();
		return tmp;
	}
	
	public void deleteTaiKhoan(long index){
		_Db.delete(TABLE, Id+"="+index, null);
	}
	
	public void update(TaiKhoan tk){
		ContentValues cv = new ContentValues();
		cv.put(SoTk, tk.get_SoTk());
		cv.put(LoaiTienId, tk.get_LoaiTienId());
		cv.put(SoDuTk, tk.get_SoDuTk());
		cv.put(HanMucThang, tk.get_HanMucThang());
		cv.put(LoaiTaiKhoan, tk.get_LoaiTkId());
		cv.put(NgayNhap, tk.get_NgayNhap().getTimeInMillis());
		cv.put(NgayCapNhatCuoi, tk.get_NgayCapNhatCuoi().getTimeInMillis());
		cv.put(GhiChu, tk.get_GhiChu());
		cv.put(Icon, tk.IconId);
		_Db.update(TABLE, cv, Id+"="+tk.get_Id(),null);
	}
	
	/*************** DbHelper *************/
private static class DbHelper extends SQLiteOpenHelper {
		
		public DbHelper(Context c){
			super(c,DATABASE_NAME , null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			String sql = "CREATE TABLE " + TABLE + " ("
					+ Id + " INTEGER PRIMARY KEY AUTOINCREMENT, "
					+ SoTk + " TEXT, "
					+ LoaiTienId + " INTEGER, "
					+ SoDuTk + " REAL, "
					+ HanMucThang + " REAL, "
					+ LoaiTaiKhoan + " INTEGER, "
					+ NgayNhap + " INTEGER, "
					+ NgayCapNhatCuoi + " INTEGER, "
					+ GhiChu + " TEXT, "
					+ Icon + " INTEGER "
			+ ");";
			db.execSQL(sql);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			String sql = " DROP TABLE IF EXISTS " + TABLE;
			db.execSQL(sql);
			onCreate(db);
		}
		
	}
}